﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using System.Data;

namespace Garlic.DAL
{
    public class StVesselBerthesDAL
    {
        private const string ST_VESSEL_BERTHES_SELECT_FOR_TABLE =
           @"select a.vbt_pbthdt,
                     a.vbt_pdptdt,
                     a.vbt_pbthdraft,
                     a.vbt_pbthdraft_a,
                     a.vbt_pbthdraft_p,
                     a.vbt_pdptdraft,
                     a.vbt_pdptdraft_a,
                     a.vbt_pdptdraft_p,
                     a.vbt_berthtimes,
                     a.vbt_ldunldfg,
                     a.vbt_status,
                     b.cst_cstm,
                     d.vsl_enname,
                    d.vsl_cnname,
                     c.scd_ivoyage || '/' || c.scd_evoyage as scd_ie_voyage,
                     a.vbt_id,
                     a.vbt_scd_id,
                     rc.rv_meaning as VBT_STATUS_MEANING
            from st_vessel_berthes a inner join
                 st_customers b on b.cst_id = a.vbt_ter_cst_id inner join
                 st_schedules c on c.scd_id = a.vbt_scd_id inner join
                 st_vessels d on d.vsl_id = c.scd_vsl_id  left join
                 st_ref_codes rc on rc.rv_low_value= a.vbt_status and rc.rv_domain ='BERTH_STATUS' 
                {0} ";

        /// <summary>
        /// GetTable
        /// </summary>
        /// <param name="textWhere">conditon</param>
        /// <param name="orderText">sort</param>
        /// <returns></returns>
        public DataTable GetTable(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
                query += " where " + textWhere;
            if (!string.IsNullOrEmpty(orderText))
                query += " order by " + orderText;

            return GetDataTable(string.Format(ST_VESSEL_BERTHES_SELECT_FOR_TABLE, query));
        }

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(dbCommand);
            if (dt != null)
            {
                dt.TableName = "ST_VESSEL_BERTHES";
            }
            return dt;
        }

    }//end class
}
